SQL-notes
SQL
Recursos
How to learn SQL for free | Roadmap to learning SQL — techTFQ
Canales de youtube
SQL for Beginners
SQL Basics Tutorial for Beginners (Practice SQL Queries) — techTFQ
Learn SQL In Only 1 Hour (EXAMPLES INCLUDED) - Ryan Nolan Data
FULL SQL DATABASE COURSE | Beginner to Intermediate in 1 Hour - Data with mo
Databases
Los data frames y los tibbles no tienen índices, pero los data.tables sí, que es una de las razones por las que son tan rápidos.
Databases are run by database management systems (DBMS’s for short).
Client-server DBMS’s: PostgreSQL, MariaDB, SQL Server.
Cloud DBMS’s, like Snowflake, Amazon’s RedShift, and Google’s BigQuery, are similar to client server DBMS’s, but they run in the cloud.
In-process DBMS’s, like SQLite or duckdb, run entirely on your computer
Conectarse a bases de datos con R
Conectarse
Code
library(rio)
library(here)
library(janitor)
billydata <- rio::import(here::here("data/BillyStringsTour.csv"))
baseballdata <- rio::import(here::here("data/BaseballStats.csv")) |> janitor::clean_names()
nycdata <- rio::import(here::here("data/AB_NYC_2019.csv"))
employeedata <- rio::import(here::here("data/Employee_202311211119.csv")) Cargar datos
dbCreateTable() no usa los datos de la plantilla, solo los nombres y tipos de variables.
Code
DBI::dbCreateTable(con, "BillyStringsTour", billydata)
DBI::dbAppendTable(con, "BillyStringsTour", billydata)
DBI::dbCreateTable(con, "BaseballStats", baseballdata)
DBI::dbAppendTable(con, "BaseballStats", baseballdata)
DBI::dbCreateTable(con, "nycdata", nycdata)
DBI::dbAppendTable(con, "nycdata", nycdata)
DBI::dbCreateTable(con, "employeedata", employeedata)
DBI::dbAppendTable(con, "employeedata", employeedata)
dbListTables(con)#> [1] "BaseballStats" "BillyStringsTour" "employeedata" "nycdata"
Ejemplos
Select, where, group by…
Code
dbGetQuery(con, "SELECT * FROM BaseballStats")Select, Distint, order by, where
Code
library(flextable)
dbGetQuery(con,
"SELECT DISTINCT TEAM
FROM BaseballStats
ORDER BY TEAM ASC
")Code
dbGetQuery(con,
"-- Jugadores de los dodgers or padres con más de 135 hits ordenados por player name
SELECT PLAYER, TEAM, HITS_HITS AS HITS
FROM BaseballStats
WHERE HITS > 135
AND (TEAM = 'Dodgers' or TEAM = 'Padres') -- EQUIVALENTE A AND(TEAM IN ('Padres', 'Dodgers'))
ORDER BY PLAYER ASC
")COUNT, AVG, HAVING, SUM
Code
dbGetQuery(con, "
-- Teams that have an AVG HRS OVER 10 WHERE TEAMS START WITH P FROM NL LEAGUE
SELECT TEAM, AVG(home_runs) AVHR
FROM BaseballStats bs
WHERE league = 'NL' AND team LIKE 'P%'
GROUP BY team
HAVING AVHR > 10
ORDER BY AVHR DESC
"
)Code
dbGetQuery(con, "
-- Show Teams that have over 2 players in the table
SELECT team, COUNT(*) NPLAYERS -- COUNT(PLAYERS)
FROM BaseballStats bs
GROUP BY team
HAVING NPLAYERS > 2
"
)Si piden contar algo dentro de algo utilizar where y count:
Code
dbGetQuery(con, "
SELECT COUNT(player)
FROM BaseballStats bs
WHERE team IN ('Padres', 'Angels','Dodgers')
"
)Code
dbGetQuery(con, "
-- show Teams that have a sum of 350 hit and are in the NL
SELECT team, SUM(hits_hits) HitSum
FROM BaseballStats bs
WHERE league = 'NL'
GROUP by team
HAVING HitSum > 350"
)CASE WHEN
Code
dbGetQuery(con, "
-- CASE WHEN EXAMPLE
SELECT player , home_runs,
CASE WHEN home_runs > 60 THEN 'MVP'
WHEN home_runs BETWEEN 59 AND 40 THEN 'CRACK'
WHEN home_runs > 20 THEN 'BUENO'
ELSE 'NORMALITO' END AS TypeOfPlayer
FROM BaseballStats bs"
)ROUND, CAST y obtener %
Queremos calcular % de entradas vendidas en función de la capacidad. si dividimos entradas/capacidad nos da 0 o 1 ya que el tipo de dato no es decimal. Por ello multiplicamos por 100 para ver % y convertimos a float. Por último redondeamos.
Code
# OJO por alguna razón me acepta las comillas dobles pero no las simples en la selección de las columnas
dbGetQuery(con, '
SELECT Venue, ROUND(CAST(100.00 * "Tickets Sold" / "Capacity" AS FLOAT),2) SELLOUTPERC
FROM BillyStringsTour bst '
)SQL WITH Clause | (Common Table Expression)
SQL WITH Clause Tutorial | (Common Table Expression) - Ryan Nolan
Code
# OJO por alguna razón me acepta las comillas dobles pero no las simples en la selección de las columnas. En los IDEs de SQL comunes cambiamos las "", por ''
dbGetQuery(con, '
-- Venue and Songs played where 95% of concert tickets were sold
WITH percentsold as (
SELECT "Concert Num", ROUND(CAST(100.00*"Tickets Sold" / "Capacity" AS FLOAT),2) AS ConcertPercentSold
FROM BillyStringsTour bst
)
SELECT bst."Venue", bst."Songs Played", p."ConcertPercentSold"
FROM BillyStringsTour bst
INNER JOIN percentsold p on p."Concert Num" = bst."Concert Num" AND p."ConcertPercentSold" > 95'
)Code
# OJO por alguna razón me acepta las comillas dobles pero no las simples en la selección de las columnas. En los IDEs de SQL comunes cambiamos las "", por ''
dbGetQuery(con, '
-- Venue and songs played where ticket sales where above averagge
WITH avgsal as (
SELECT AVG("Tickets Sold") AS avgticketsales
FROM BillyStringsTour bst
)
SELECT bst."Venue", bst."Songs Played", av."avgticketsales", bst."Tickets Sold"
FROM BillyStringsTour bst
INNER JOIN avgsal av on bst."Tickets Sold"> av."avgticketsales"
ORDER BY bst."Songs Played" DESC
'
)FECHAS
Extract
Extraer meses, años días, de columnas con fechas.
Code
dbGetQuery(con, '
SELECT
HireDate,
EXTRACT(YEAR FROM "HireDate") AS year,
EXTRACT(MONTH FROM "HireDate") AS month,
EXTRACT(DAY FROM "HireDate") AS day
FROM
Employeedata;
-- EN SLQLITE SERÍA
-- SELECT
-- FROM Employee e
-- SELECT
-- HireDate,
-- strftime("%Y", HireDate) AS year,
-- strftime("%m", HireDate) AS month,
-- strftime("%d", HireDate) AS day
-- FROM
-- Employeedata;
'
)DateDiff
DateDiff(datetype, start, end)
DateDiff(year, Date, GETDATE()) También puedes en la clausula where DateDiff(year, Date, GETDATE()) = 1 y te saca los de un año de diferencia
STRINGS
Substring, position, coalesce
Substring, extraer caracteres por su posición
Code
dbGetQuery(con, '
-- get the email from 5th character
SELECT
email,
SUBSTRING(email from 5)
FROM
Employeedata;
'
)Position: posición de un caracter
Code
dbGetQuery(con, "
-- get position of a character
SELECT
email,
POSITION('@' IN email) as position
FROM
Employeedata;
"
)Coalesce to fill mising emails with custom value
Code
dbGetQuery(con, "
-- Coalesce to fill mising emails with custom value
SELECT
email,
COALESCE(email,'NO EMAIL PROVIDED')
FROM
Employeedata;
"
)String manipulation formulas
Code
# Crear la tabla temporal con nombres de columna que contienen espacios
dbExecute(con, "
CREATE TEMPORARY TABLE '#textformat' (
example1 varchar(10),
example2 varchar(10),
example3 varchar(10)
);
")#> [1] 0
Code
# Insertar datos en la tabla temporal
dbExecute(con, "
INSERT INTO '#textformat' VALUES (
' Iker ', ' Jimenez ', ' misterio'
);
")#> [1] 1
Code
dbGetQuery(con, "SELECT * FROM '#textformat'")Code
# Len
dbGetQuery(con, "SELECT LEN(example2) FROM '#textformat'")Code
# TRIM, LTRIM, RTRIM
dbGetQuery(con, "SELECT
TRIM(example2) as TRIM,
LTRIM(example2) as LTRIM,
RTRIM(example2) as RTRIM
FROM '#textformat'")Code
# Replace
dbGetQuery(con, "SELECT TRIM(REPLACE(example2, 'z', 's')) FROM '#textformat'")Code
## UPER, LOWE
dbGetQuery(con, "SELECT UPPER(example3) FROM '#textformat'")Code
dbGetQuery(con, "SELECT LOWER(example3) FROM '#textformat'")Code
# concat
dbGetQuery(con, "SELECT CONCAT(example1,' , ', example2) FROM '#textformat'")Code
dbGetQuery(con, "SELECT CONCAT_WS('- ', example1, example2, 'Cuatro') FROM '#textformat'")Code
# Para tapar por ejemplo números de telefono por confidencialidad. Muestras el número de caracteres que quieras
# Right left
dbGetQuery(con, "SELECT Left(TRIM(example1),2) FROM '#textformat'")Code
# Substring(expresion, start, lenght)
dbGetQuery(con, "SELECT SUBSTRING(TRIM(example1),2,3) FROM '#textformat'")Code
# Window functions Over, Over partition, ranks, led, lag
SQL Window Functions in 10 Minutes - YouTube
WINDOW FUNCTIONS| Advanced SQL
Si calculas algo con alguna window function y quieres una columna que lo integre para cada registro utilizamos OVER()
Code
dbGetQuery(con, "
SELECT
MAX(price)
FROM nycdata;
"
)Code
dbGetQuery(con, "
-- Calculamos media, max y min de los precios y los ponemos para cada registro
SELECT
id,
name,
neighbourhood_group,
AVG(price) OVER(),
MAX(price) OVER(),
MIN(price) OVER(),
FROM nycdata;
"
)Code
dbGetQuery(con, "
-- Percent of average price with OVER()
SELECT
id,
host_name,
neighbourhood_group,
price,
ROUND(AVG(price) OVER(), 2) AS avg_price,
ROUND((price / AVG(price) OVER()), 2) AS diff_from_avg_price,
ROUND((price / AVG(price) OVER() * 100), 2) AS percent_of_avg_price,
ROUND((price / AVG(price) OVER() - 1) * 100, 2) AS percent_diff_from_avg_price
FROM nycdata;
"
)Partition BY: lo que calcularias con un group by, pero inetertandolo en cada registro.
Code
dbGetQuery(con, "
SELECT
id,
host_name,
neighbourhood_group,
neighbourhood,
price,
AVG(price) OVER(PARTITION BY neighbourhood_group) AS avg_price_by_neigh_group,
AVG(price) OVER(PARTITION BY neighbourhood_group, neighbourhood) AS avg_price_by_group_and_neigh,
ROUND(price - AVG(price) OVER(PARTITION BY neighbourhood_group), 2) AS neigh_group_delta,
ROUND(price - AVG(price) OVER(PARTITION BY neighbourhood_group, neighbourhood), 2) AS group_and_neigh_delta,
FROM nycdata;
"
)Code
dbGetQuery(con, "
-- PARTITION BY
-- count partition by players on a team. show partition by avg() batting average in the leage
SELECT
player,
team,
COUNT(player) OVER(PARTITION BY team) AS Playersonteam,
league,
ROUND(AVG(batting_avg) OVER (PARTITION BY league),2) LeagueAVG
FROM BaseballStats;
"
)Code
dbGetQuery(con, "
-- PARTITION BY
-- show partition by avg() batting average in the leage
SELECT
player,
league,
ROUND(AVG(batting_avg) OVER (PARTITION BY league),2) LeagueAVG
FROM BaseballStats;
"
)SQL Rank, Dense Rank, and Row Number Tutorial
- row number númera las filas
- rank y dense_rank, da un rankin. RANK() en SQL asigna un rango único a cada fila, saltando números en caso de empate (3,4,4,6), mientras que DENSE_RANK() asigna rangos únicos sin saltar números en caso de empate, manteniendo la continuidad de los rangos (3,4,4,5,6).
Code
dbGetQuery(con, "
-- Usa estas funciones para ver los HR leaders 2022 dentro de la league desc
SELECT
player,
league,
home_runs,
ROW_NUMBER() OVER(PARTITION BY league ORDER BY home_runs DESC) RowNumHR,
RANK() OVER(PARTITION BY league ORDER BY home_runs DESC) RankHR,
DENSE_RANK() OVER(PARTITION BY league ORDER BY home_runs DESC) DenseRankHR,
FROM BaseballStats;
"
)Lead, lag
- Lag: Muestra el valor previo
- Lead: Muestra el siguiente valor
El numero contiguo indica cuantas posiciones, y el siguiente que numero poner para reemplazar los null
Code
dbGetQuery(con, "
SELECT
host_name,
price,
LEAD(price,2) OVER(PARTITION BY host_name ORDER BY last_review) AS leadprice,
LAG(price,2) OVER(PARTITION BY host_name ORDER BY last_review) AS lagprice,
LAG(price,2,0) OVER(PARTITION BY host_name ORDER BY last_review) AS lagprice,
FROM nycdata;
"
)Suele utilizarse para comprobar tendencias entre años. Ejemplos:
Code
# Con los datos de baseball, compararar los HR de cada jugador por liga y comparalos con los dos siguientes
dbGetQuery(con, "
SELECT
player,
league,
home_runs,
LEAD(home_runs) OVER (PARTITION BY league ORDER BY home_runs DESC) HR1,
LEAD(home_runs, 2) OVER (PARTITION BY league ORDER BY home_runs DESC) HR2,
FROM BaseballStats
"
)FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE, CUME_DIST and PERCENT_RANK.
FIRST_VALUE: Retorna el primer valor de una expresión en un conjunto ordenado de filas según la cláusula ORDER BY.LAST_VALUE: Devuelve el último valor de una expresión en un conjunto ordenado de filas según la cláusula ORDER BY.NTH_VALUE: Retorna el valor de una expresión en la posición N dentro de un conjunto ordenado de filas, especificado por la cláusula ORDER BY.NTILE: Divide el conjunto ordenado de filas en un número especificado de “buckets” o grupos (tiles) y asigna un número de baldosa a cada fila, indicando a qué grupo pertenece.CUME_DIST: Calcula la función de distribución acumulativa para una fila dada en un conjunto ordenado de filas.PERCENT_RANK: Calcula la posición relativa de una fila dentro de un conjunto ordenado de filas como un valor porcentual, proporcionando una medida de su posición en comparación con las demás filas.
5 Practical Examples of Using ROWS BETWEEN in SQL | LearnSQL.com
Stored Procedures
(SQL Stored Procedures SIMPLIFIED (3 Examples!) - YouTube
Un Stored Procedure (Procedimiento Almacenado) en SQL es un conjunto de instrucciones SQL predefinidas y almacenadas en la base de datos. Estas instrucciones pueden realizar operaciones complejas, procesar datos, ejecutar consultas, y realizar otras tareas específicas. Los Stored Procedures están guardados en la base de datos y pueden ser invocados por otras aplicaciones o scripts SQL.
Las características clave de los Stored Procedures en SQL incluyen:
Reusabilidad: Pueden ser llamados y ejecutados repetidamente desde diferentes partes de una aplicación o desde distintas aplicaciones, lo que promueve la reutilización del código.
Parámetros: Pueden aceptar parámetros de entrada y/o salida, permitiendo la personalización dinámica de las operaciones que realizan.
Optimización de Desempeño: Al estar almacenados en el servidor de la base de datos, reducen la necesidad de transmitir grandes volúmenes de datos entre la aplicación y la base de datos, mejorando así el rendimiento.
Seguridad: Ayudan a controlar el acceso a los datos y operaciones en la base de datos, ya que los usuarios pueden ejecutar el procedimiento sin necesidad de tener acceso directo a las tablas subyacentes.
Transacciones: Pueden agrupar varias instrucciones SQL en una transacción, asegurando la consistencia de la base de datos.
Mantenimiento: Facilitan la gestión y actualización de la lógica de negocio, ya que los cambios se realizan en un único lugar.
En resumen:
Stored Procedure (Procedimiento Almacenado):
Es un conjunto de instrucciones SQL almacenado en la base de datos.
Puede realizar operaciones complejas, procesar datos, aceptar parámetros y ejecutar lógica de programación dentro de la base de datos.
Se puede utilizar para realizar tareas como actualizaciones, inserciones, eliminaciones y consultas más complejas.
Se ejecuta explícitamente mediante una llamada desde una aplicación o un script.
SQL WITH clause CTE vs temporary table
SQL Temp Tables Tutorial (Examples Included) - YouTube
Expresiones de Tabla Común (CTE)
Una Expresión de Tabla Común (CTE) es un conjunto de resultados temporal con nombre en SQL, referenciado dentro de declaraciones SELECT, INSERT, UPDATE o DELETE. Definida con la instrucción WITH, puede ser recursiva y es conocida por simplificar consultas complejas, mejorar la legibilidad y facilitar el mantenimiento. Las CTE no se almacenan físicamente en disco y existen solo durante la ejecución de una única consulta. No se pueden crear, alterar ni eliminar explícitamente y no se pueden referenciar desde múltiples consultas dentro del mismo lote.
Ejemplo:
WITH TopEmployees AS (
SELECT TOP 10
EmployeeID,
FirstName,
LastName,
Salary
FROM Employees
ORDER BY Salary DESC
)
SELECT
FirstName + ' ' + LastName AS NombreCompleto,
Salary
FROM TopEmployees;Tablas Temporales
Las Tablas Temporales también son conjuntos de resultados temporales almacenados en la base de datos del sistema tempdb. A diferencia de las CTE, las tablas temporales se almacenan físicamente en disco y se pueden crear, alterar o eliminar explícitamente. Son útiles para almacenar y manipular grandes cantidades de datos, y son accesibles desde múltiples consultas dentro del mismo lote.
Ejemplo:
CREATE TABLE #TopEmployees (
EmployeeID INT,
FirstName NVARCHAR(50),
LastName NVARCHAR(50),
Salary DECIMAL(10,2)
);
INSERT INTO #TopEmployees
SELECT TOP 10
EmployeeID,
FirstName,
LastName,
Salary
FROM Employees
ORDER BY Salary DESC;
SELECT
FirstName + ' ' + LastName AS NombreCompleto,
Salary
FROM #TopEmployees;
DROP TABLE #TopEmployees;Diferencias entre CTEs y Tablas Temporales
- Almacenamiento: Las CTE no se almacenan en disco, mientras que las tablas temporales sí.
- Duración: Las CTE existen solo durante la ejecución de una consulta, las tablas temporales pueden persistir más allá.
- Gestión Explícita: No se puede gestionar explícitamente las CTE, mientras que sí se puede con tablas temporales.
- Ámbito: Las CTE solo son accesibles dentro de la consulta que las define; las tablas temporales pueden ser accedidas por múltiples consultas en el mismo lote.
Cuándo usar CTEs vs. Tablas Temporales
CTEs: Preferibles para simplificar consultas complejas y mejorar la legibilidad, especialmente al referenciar el mismo conjunto de resultados varias veces dentro de una consulta.
Tablas Temporales: Útiles para almacenar y manipular grandes cantidades de datos, referenciándolos en múltiples consultas dentro del mismo lote, y para optimizar el rendimiento mediante la indexación.
Conclusión
Las CTEs y las Tablas Temporales son herramientas valiosas en SQL Server, ofreciendo ventajas distintas. Comprender sus diferencias permite la optimización eficiente de consultas, mejorando tanto el rendimiento como la legibilidad.
UNION VS UNION ALL
UNION La instrucción UNION se utiliza para seleccionar los datos relacionados entre dos tablas. Las columnas tienen que ser del mismo tipo de datos. Solo se devuelven los valores distintos
UNION ALL La instrucción UNION ALL es similar a UNION con la excepción que se seleccionan todos los valores.
UNION es útil cuando deseas obtener un conjunto de resultados único sin duplicados. UNION ALL es útil cuando deseas incluir todos los registros, incluso los duplicados
Cuando utilizas la operación UNION en dos tablas similares, A y B, con cláusulas WHERE, estás aplicando una condición de ‘o’ entre las condiciones de la tabla A y la tabla B. Esto significa que obtendrás un conjunto de resultados sin duplicados, ya que la operación eliminará automáticamente registros duplicados.
Por otro lado, la operación UNION ALL presenta un comportamiento diferente. Esta operación retorna primero los resultados que cumplen con la condición de la tabla A y, a continuación, los resultados que cumplen con la condición de la tabla B. Esto puede generar duplicados en el resultado final si un mismo registro cumple con ambas condiciones.
En resumen, UNION elimina duplicados y actúa como una condición de ‘o’, mientras que UNION ALL muestra todos los resultados, produciendo duplicados si un registro cumple con ambas condiciones.
SQL VIEW
SQL View (Vista SQL):
Es una consulta SQL predefinida que se almacena en la base de datos y que actúa como una tabla virtual.
Representa una “vista” lógica de los datos, que puede consistir en una o más tablas, y muestra los resultados de una consulta específica.
No almacena datos físicamente; en cambio, proporciona una representación virtual de los datos.
Se utiliza principalmente para simplificar consultas complejas, ocultar detalles de la estructura de las tablas y mejorar la seguridad al limitar el acceso directo a las tablas subyacentes.
En resumen, mientras que los Stored Procedures son conjuntos de instrucciones que realizan operaciones y pueden aceptar parámetros, las SQL Views son consultas predefinidas que proporcionan una vista lógica de los datos sin almacenarlos físicament
NOTA: Al crearla o alterarla no puedes utilizar order by. tienes que llamarla y ahi ya puedes
Supongamos que tienes una base de datos simple con dos tablas: una tabla llamada Clientes y otra llamada Pedidos. Cada cliente puede realizar varios pedidos. Ahora, vamos a crear una vista que muestre la información del cliente y la cantidad total de pedidos que ha realizado. Luego, la utilizaremos para realizar una consulta con ORDER BY para ordenar los resultados.
- Creación de las tablas y datos de ejemplo:
CREATE TABLE Clientes (
ClienteID INT PRIMARY KEY,
Nombre VARCHAR(50)
);
CREATE TABLE Pedidos (
PedidoID INT PRIMARY KEY,
ClienteID INT,
FechaPedido DATE,
Monto DECIMAL(10, 2),
FOREIGN KEY (ClienteID) REFERENCES Clientes(ClienteID)
);
INSERT INTO Clientes VALUES (1, 'Juan');
INSERT INTO Clientes VALUES (2, 'María');
INSERT INTO Pedidos VALUES (1, 1, '2023-01-01', 100.00);
INSERT INTO Pedidos VALUES (2, 1, '2023-02-01', 150.00);
INSERT INTO Pedidos VALUES (3, 2, '2023-02-15', 200.00);- Creación de la vista:
CREATE VIEW VistaClientesPedidos AS
SELECT
c.ClienteID,
c.Nombre,
COUNT(p.PedidoID) AS TotalPedidos
FROM
Clientes c
LEFT JOIN
Pedidos p ON c.ClienteID = p.ClienteID
GROUP BY
c.ClienteID, c.Nombre;- Uso de la vista con
ORDER BY:
-- Consulta utilizando la vista y ordenando por el total de pedidos
SELECT * FROM VistaClientesPedidos
ORDER BY TotalPedidos DESC;En este ejemplo, la vista VistaClientesPedidos muestra la información del cliente y la cantidad total de pedidos que ha realizado. Luego, la consulta utiliza la vista y ordena los resultados en orden descendente según el total de pedidos.
Para cambiar la tabla de vista cambiar create por alter.
SQL Unpivot Simplified (Turn Columns into Rows)
SQL Unpivot Simplified (Turn Columns into Rows) - YouTube
La operación UNPIVOT en SQL se utiliza para transformar columnas en filas. Aquí hay un ejemplo sencillo de cómo realizar UNPIVOT en SQL:
Supongamos que tienes una tabla llamada Ventas con la siguiente estructura y datos:
CREATE TABLE Ventas (
Producto VARCHAR(50),
Enero INT,
Febrero INT,
Marzo INT
);
INSERT INTO Ventas VALUES
('ProductoA', 100, 150, 120),
('ProductoB', 80, 90, 110),
('ProductoC', 200, 180, 220);La tabla original se ve así:
| Producto | Enero | Febrero | Marzo |
|------------|-------|---------|-------|
| ProductoA | 100 | 150 | 120 |
| ProductoB | 80 | 90 | 110 |
| ProductoC | 200 | 180 | 220 |
Ahora, queremos UNPIVOTAR la tabla para obtener una estructura en la que cada fila represente un mes de ventas para un producto específico. Aquí está cómo hacerlo:
-- Operación UNPIVOT
SELECT
Producto,
Mes,
Ventas
FROM
Ventas
UNPIVOT (
Ventas FOR Mes IN (Enero, Febrero, Marzo)
) AS UnpivotedVentas;La salida después de la operación UNPIVOT sería:
-- Operación UNPIVOT
SELECT
Producto,
Mes,
Ventas
FROM
Ventas
UNPIVOT (
Ventas FOR Mes IN (Enero, Febrero, Marzo)
) AS UnpivotedVentas;| Producto | Mes | Ventas |
|------------|---------|--------|
| ProductoA | Enero | 100 |
| ProductoA | Febrero | 150 |
| ProductoA | Marzo | 120 |
| ProductoB | Enero | 80 |
| ProductoB | Febrero | 90 |
| ProductoB | Marzo | 110 |
| ProductoC | Enero | 200 |
| ProductoC | Febrero | 180 |
| ProductoC | Marzo | 220 |SQL PIVOTS Made Easy (Rows to Columns)
SQL PIVOTS Made Easy (Rows to Columns) - YouTube
Si cogemos el resultado del ejemplo anterior, podemos volver al estado original de la siguiente forma
-- Operación PIVOT
SELECT
Producto,
[Enero],
[Febrero],
[Marzo]
FROM (
SELECT
Producto,
Mes,
Ventas
FROM
Ventas
) AS VentasUnpivoted
PIVOT (
SUM(Ventas) FOR Mes IN ([Enero], [Febrero], [Marzo])
) AS VentasPivoted;SQL MERGE Tutorial With Example
SQL MERGE Tutorial With Example - YouTube
La operación MERGE en SQL se utiliza para combinar datos de dos tablas, actualizando o insertando registros en función de ciertas condiciones. Aquí tienes un ejemplo muy sencillo:
Supongamos que tienes dos tablas: Clientes y NuevosClientes. La tabla Clientes ya contiene algunos registros, y la tabla NuevosClientes contiene información sobre clientes nuevos que quieres agregar o actualizar en la tabla original.
-- Creación de la tabla Clientes
CREATE TABLE Clientes (
ClienteID INT PRIMARY KEY,
Nombre VARCHAR(50),
CorreoElectronico VARCHAR(50)
);
-- Inserción de datos de ejemplo en la tabla Clientes
INSERT INTO Clientes VALUES
(1, 'Juan', 'juan@email.com'),
(2, 'María', 'maria@email.com');
-- Creación de la tabla NuevosClientes
CREATE TABLE NuevosClientes (
ClienteID INT PRIMARY KEY,
Nombre VARCHAR(50),
CorreoElectronico VARCHAR(50)
);
-- Inserción de datos de ejemplo en la tabla NuevosClientes
INSERT INTO NuevosClientes VALUES
(1, 'Juan', 'juan_actualizado@email.com'),
(3, 'Pedro', 'pedro@email.com');Ahora, vamos a utilizar la operación MERGE para combinar estos datos. La idea aquí es actualizar la información del cliente si ya existe en la tabla Clientes, o insertarla si es un cliente nuevo.
-- Operación MERGE
MERGE INTO Clientes AS Target
USING NuevosClientes AS Source
ON Target.ClienteID = Source.ClienteID
WHEN MATCHED THEN
UPDATE SET
Target.Nombre = Source.Nombre,
Target.CorreoElectronico = Source.CorreoElectronico
WHEN NOT MATCHED THEN
INSERT (ClienteID, Nombre, CorreoElectronico)
VALUES (Source.ClienteID, Source.Nombre, Source.CorreoElectronico);en este ejemplo:
Utilizamos MERGE INTO para especificar la tabla de destino (Clientes) y la tabla fuente (NuevosClientes). La condición ON especifica cómo las filas se deben emparejar para la actualización (MATCHED) o inserción (NOT MATCHED). Cuando se encuentra una coincidencia (MATCHED), actualizamos la información del cliente en la tabla Clientes. Cuando no se encuentra una coincidencia (NOT MATCHED), insertamos el nuevo cliente en la tabla Clientes. Después de ejecutar esta operación MERGE, la tabla Clientes se actualizará según los datos de la tabla NuevosClientes. En este caso, Juan tendrá su dirección de correo electrónico actualizada, y Pedro será insertado como un nuevo cliente.
puedes agregar una condición adicional con AND en la cláusula ON de la operación MERGE. Por ejemplo, si deseas emparejar las filas por ClienteID y Nombre, puedes hacerlo de la siguiente manera:
-- Operación MERGE con condición adicional
MERGE INTO Clientes AS Target
USING NuevosClientes AS Source
ON Target.ClienteID = Source.ClienteID AND Target.Nombre = Source.Nombre
WHEN MATCHED THEN
UPDATE SET
Target.CorreoElectronico = Source.CorreoElectronico
WHEN NOT MATCHED THEN
INSERT (ClienteID, Nombre, CorreoElectronico)
VALUES (Source.ClienteID, Source.Nombre, Source.CorreoElectronico);En este caso, la condición adicional en la cláusula ON especifica que las filas deben coincidir tanto por ClienteID como por Nombre. La operación MERGE ahora actualizará las filas existentes en la tabla Clientes si coinciden en ambas columnas y, en caso contrario, insertará nuevas filas en la tabla Clientes.
COALESCE
How to Use the COALESCE Function in SQL - YouTube
El uso de COALESCE es comúnmente para manejar valores nulos en SQL. Aquí tienes un ejemplo mínimo utilizando COALESCE:
Supongamos que tienes una tabla llamada Empleados que almacena información sobre empleados, incluido su salario y una bonificación. Sin embargo, algunos empleados pueden no tener asignada una bonificación, y sus valores pueden ser NULL.
-- Creación de la tabla Empleados
CREATE TABLE Empleados (
EmpleadoID INT PRIMARY KEY,
Nombre VARCHAR(50),
Salario DECIMAL(10, 2),
Bonificacion DECIMAL(10, 2)
);
-- Inserción de datos de ejemplo en la tabla Empleados
INSERT INTO Empleados VALUES
(1, 'Juan', 50000.00, NULL),
(2, 'María', 60000.00, 2000.00),
(3, 'Pedro', 55000.00, NULL);Ahora, supongamos que deseas obtener el salario total, incluyendo la bonificación, pero tratando los valores de bonificación nulos como si fueran 0. Puedes utilizar COALESCE para manejar esto.
-- Consulta utilizando COALESCE
SELECT
EmpleadoID,
Nombre,
Salario,
COALESCE(Bonificacion, 0) AS Bonificacion
FROM
Empleados;En esta consulta, COALESCE(Bonificacion, 0) se utiliza para tomar el valor de la bonificación. Si la bonificación es NULL, se reemplaza por 0. La salida de esta consulta mostrará el salario y la bonificación, tratando los valores de bonificación nulos como 0:
| EmpleadoID | Nombre | Salario | Bonificacion |
|------------|--------|-----------|--------------|
| 1 | Juan | 50000.00 | 0.00 |
| 2 | María | 60000.00 | 2000.00 |
| 3 | Pedro | 55000.00 | 0.00 |Esta es una forma simple de utilizar COALESCE para manejar valores nulos y proporcionar un valor predeterminado en caso de que se encuentre un NULL.